/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package monitoring.dbpersistence.util;

import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import monitoring.beans.database.MonitoringDB;
import monitoring.beans.util.FilterBean;
//import sun.org.mozilla.javascript.internal.regexp.SubString;

/**
 *
 * @author Regulus
 */
public class SQLProcessing {

    private String select = "select basisofrecord,"
            + "idgeral,"
            + "institutioncode,"
            + "collectioncode,"
            + "catalognumber,"
            + "scientificname,"
            + "denomination,"
            + "technicalcollection,"
            + "digitizer,"
            + "collector,"
            + "decimallatitude,"
            + "decimallongitude,"
            + "country,"
            + "stateprovince,"
            + "municipality,"
            + "supporttype, "
            + "locality,"
            + "culture,"
            + "cultivar,"
            + "surroundingsculture,"
            + "predominantbiome,"
            + "installationdate,"
            + "installationtime,"
            + "collectdate,"
            + "collecttime,"
            + "surroundingsvegetation,"
            + "plotnumber,"
            + "amostralnumber,"
            + "colorpantrap,"
            + "tipodesuporte,"
            + "floorheight,"
            + "\"order\","
            + "\"family\","
            + "genus,"
            + "subgenus,"
            + "speciesname,"
            + "subspecies, "
            + "tribe,"
            + "subtribe,"
            + "vernacularname,"
            + "verbatimelevation,"
            + "geodeticdatum,"
            + "locationremark,"
            + "coordinateprecision,"
            + "referencepoints,"
            + "sex,"
            + "weight,"
            + "width,"
            + "length,"
            + "height";
    private String from = " from monitoring m "
            + "left join collector ON m.idcollector = collector.idcollector "
            + "left join denomination  ON m.iddenomination = denomination.iddenomination "
            + "left join technicalcollection ON m.idtechnicalcollection = technicalcollection.idtechnicalcollection "
            + "left join surroundingsvegetation ON m.idsurroundingsvegetation = surroundingsvegetation.idsurroundingsvegetation "
            + "left join digitizer ON m.iddigitizer = digitizer.iddigitizer "
            + "left join culture ON m.idculture = culture.idculture "
            + "left join cultivar ON m. idcultivar = cultivar.idcultivar "
            + "left join predominantbiome ON m.idpredominantbiome = predominantbiome.idpredominantbiome "
            + "left join surroundingsculture  ON m.idsurroundingsculture = surroundingsculture.idsurroundingsculture "
            + "left join colorpantrap ON m.idcolorpantrap = colorpantrap.idcolorpantrap "
            + "left join supporttype ON m.idsupporttype = supporttype.idsupporttype "
            + "	left join geospatialelement g ON m.idgeospatialelement = g.idgeospatialelement "//element
            + "left join localityelement l ON m.idlocalityelement = l.idlocalityelement "//element
            + "	left join country ON l.idcountry = country.idcountry "
            + "	left join stateprovince ON l.idstateprovince = stateprovince.idstateprovince "
            + "	left join municipality ON l.idmunicipality =municipality.idmunicipality "
            + "	left join locality  ON l.idlocality = locality.idlocality  "
            + "left join taxonomicelement tax ON m.idtaxonomicelement = tax.idtaxonomicelement "//element
            + "	left join scientificname ON tax.idscientificname = scientificname.idscientificname "
            + "	left join \"order\" ON tax.idorder = \"order\".idorder "
            + "	left join \"family\" ON tax.idfamily = \"family\".idfamily "
            + "	left join genus ON tax.idgenus = genus.idgenus "
            + "	left join subgenus ON tax.idsubgenus = subgenus.idsubgenus "
            + " left join speciesname ON tax.idspeciesname = speciesname.idspeciesname "
            + " left join subspecies ON tax.idsubspecies = subspecies.idsubspecies "
            + " left join tribe ON tax.idtribe = tribe.idtribe "
            + " left join subtribe ON tax.idsubtribe = subtribe.idsubtribe "
            + "left join recordlevelelement rec ON m.idrecordlevelelement = rec.idrecordlevelelement "//element
            + "	left join basisofrecord ON rec.idbasisofrecord = basisofrecord.idbasisofrecord "
            + "	left join institutioncode ON rec.idinstitutioncode = institutioncode.idinstitutioncode "
            + "	left join collectioncode ON rec.idcollectioncode = collectioncode.idcollectioncode  "
            + "left join occurrenceelement ocrr ON m.idoccurrenceelement = ocrr.idoccurrenceelement "//element
            + "left join sex ON ocrr.idsex = sex.idsex";
    private String where = " WHERE ";

    public String select(List<FilterBean> bean) {
        SQLTreatement sQL = new SQLTreatement();
        if (bean.isEmpty()) {
            return select + from;
        } else {
            for (FilterBean aux : bean) {
                if ("catalognumber".equals(aux.getEntity()) || "surroundingsculture".equals(aux.getEntity())
                        || "amostralnumber".equals(aux.getEntity()) || "tipodesuporte".equals(aux.getEntity())
                        || "verbatimelevation".equals(aux.getEntity()) || "vernacularname".equals(aux.getEntity())
                        || "geodeticdatum".equals(aux.getEntity()) || "locationremark".equals(aux.getEntity())
                        || "referencepoints".equals(aux.getEntity())) {

                    aux.setValue(aux.getValue().replace("\\", "\\\\"));
                    aux.setValue(aux.getValue().replace("'", "\\'"));

                    this.where += aux.getEntity() + " = '" + aux.getValue() + "' AND ";

                } else if ("plotnumber".equals(aux.getEntity())) {
                    this.where += aux.getEntity() + " = " + aux.getValue() + " AND ";
                } else {
                    String value = aux.getValue();
                    this.where += " " + aux.getEntity() + ".id" + aux.getEntity() + " = " + aux.getValue() + " AND ";
                }

            }
            this.where = this.where.substring(0, this.where.length() - 4);
            return select + from + where;
        }
    }

    public String insert(MonitoringDB m) {
        StringBuffer insertRecordLevel;
        StringBuffer insertLocality;
        StringBuffer insertOccurrence;
        StringBuffer insertTaxonomic;
        StringBuffer insertGeospatial;
        StringBuffer insertMonitoring;
        Date d = new Date();
        
        StringBuffer gui = new StringBuffer();
        gui.append(m.getRecordLevelElement().getInstitutionCode().getInstitutionCode()).append(":").
                append(m.getRecordLevelElement().getCollectionCode().getCollectionCode()).append(":").
                append(m.getOcurrenceElement().getCatalogNumber());
        SimpleDateFormat md = new SimpleDateFormat("yyyy-MM-dd");
        
        insertRecordLevel = new StringBuffer(" (SELECT saverecordlevel_monitoring(");
        insertRecordLevel.append("'").append(md.format(new Date())).append("', '").
                append(m.getRecordLevelElement().getInstitutionCode().getIdInstitutionCode()).append("', '").
                append(m.getRecordLevelElement().getCollectionCode().getIdCollectionCode()).append("', '").
                append(m.getRecordLevelElement().getBasisOfRecord().getIdBasisOfRecord()).append("','").
                append(gui).append("')) ");
        insertGeospatial = new StringBuffer(" (SELECT savegeospatial_monitoring");
        insertGeospatial.append("('").append(m.getGeospatialElement().getDecimalLatitude()).append("', '").
                append(m.getGeospatialElement().getDecimalLongitude()).append("', '").
                append(m.getGeospatialElement().getGeodeticDatum()).append("', '").
                append(m.getGeospatialElement().getReferencePoints()).append("')) ");
        insertLocality = new StringBuffer(" (SELECT savelocality_monitoring('");
        insertLocality.append(m.getLocalityElement().getCoordinatePrecision()).append("', '").
                append(m.getLocalityElement().getCountry().getIdCountry()).append("', '").
                append(m.getLocalityElement().getLocality().getIdLocality()).append("', '").
                append(m.getLocalityElement().getLocationRemark()).append("', '").
                append(m.getLocalityElement().getMunicipality().getIdMunicipality()).append("', '").
                append(m.getLocalityElement().getStateProvince().getIdStateProvince()).append("', '").
                append(m.getLocalityElement().getVerbatimElevation()).
                append("')) ");
        StringBuffer hc = new StringBuffer();
        hc.append(m.getTaxonomicElement().getOrder().getOrder()).append(":").
                append(m.getTaxonomicElement().getTribe().getTribe()).append(":").
                append(m.getTaxonomicElement().getSubTribe().getSubTribe()).append(":").
                append(m.getTaxonomicElement().getFamily().getFamily()).append(":").
                append(m.getTaxonomicElement().getGenus().getGenus()).append(":").
                append(m.getTaxonomicElement().getSubGenus().getSubGenus()).append(":").
                append(m.getTaxonomicElement().getSpeciesName().getSpeciesName()).append(":").
                append(m.getTaxonomicElement().getSubSpecies().getSubSpecies()).append(":").
                append(m.getTaxonomicElement().getScientificName().getScientificName());
        insertTaxonomic = new StringBuffer(" (SELECT savetaxonomic_monitoring");
        insertTaxonomic.append("('").append(m.getTaxonomicElement().getFamily().getIdFamily()).append("', '").
                append(m.getTaxonomicElement().getGenus().getIdGenus()).append("', '").
                append(m.getTaxonomicElement().getOrder().getIdOrder()).append("', '").
                append(m.getTaxonomicElement().getScientificName().getIdScientificName()).append("', '").
                append(m.getTaxonomicElement().getSpeciesName().getIdSpeciesName()).append("', '").
                append(m.getTaxonomicElement().getSubGenus().getIdSubGenus()).append("', '").
                append(m.getTaxonomicElement().getSubTribe().getIdSubTribe()).append("', '").
                append(m.getTaxonomicElement().getTribe().getIdTribe()).append("', '").
                append(m.getTaxonomicElement().getSubSpecies().getIdSubSpecies()).append("', '").
                append(m.getTaxonomicElement().getVernacularName()).append("','").
                append(hc).append("')) ");
        insertOccurrence = new StringBuffer(" (SELECT saveoccurrence_monitoring");
        insertOccurrence.append("('").append(m.getOcurrenceElement().getSex().getIdSex()).append("', '").
                append(m.getOcurrenceElement().getCatalogNumber()).append("')) ");
        insertMonitoring = new StringBuffer("INSERT INTO monitoring(");
        insertMonitoring.append("iddenomination, idtechnicalcollection, iddigitizer, idculture, ");
        insertMonitoring.append("idcultivar, idpredominantbiome, installationdate, installationtime, ");
        insertMonitoring.append("collectdate, collecttime, idsurroundingsculture, plotnumber, ");
        insertMonitoring.append("amostralnumber, idcolorpantrap, idsupporttype, floorheight, weight, ");
        insertMonitoring.append("idcollector, width, length, height, idgeospatialelement, ");
        insertMonitoring.append("idlocalityelement, idtaxonomicelement, idrecordlevelelement, ");
        insertMonitoring.append("idoccurrenceelement, idsurroundingsvegetation, idgeral) ");
        
        SimpleDateFormat sdfs = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat st = new SimpleDateFormat("HH:mm");
        
        StringBuffer installationDate = new StringBuffer();
        if(m.getInstallationDate()!=null)
            installationDate.append(sdfs.format(m.getInstallationDate()));            
        else
            installationDate.append("null");
        
        StringBuffer collectDate = new StringBuffer();
        if(m.getCollectDate()!=null)
            collectDate.append(sdfs.format(m.getCollectDate()));
        else
            collectDate.append("null");
        
        StringBuffer installationTime = new StringBuffer();
        if(m.getInstallationTime()!=null)
            installationTime.append(st.format(m.getInstallationTime()));
        else
            installationTime.append("null");
        
        StringBuffer collectTime = new StringBuffer();
        if(m.getCollectTime()!=null)
            collectTime.append(st.format(m.getCollectTime()));
        else
            collectTime.append("null");
        
        insertMonitoring.append("VALUES (").
                append(m.getDenomination().getIdDenomination()).append(", ").
                append(m.getTechnicalCollection().getIdTechinicalCollection()).append(", ").
                append(m.getDigitizer().getIdDigitizer()).append(", ").
                append(m.getCulture().getIdCulture()).append(", ").
                append(m.getCultivar().getIdCultivar()).append(", ").
                append(m.getPredominantBiome().getIdPredominantBiome()).append(", ").append(m.getInstallationDate()==null?"":"'").
                append(installationDate).append(m.getInstallationDate()==null?"":"'").append(", ").append(m.getInstallationTime()==null?"":"'").
                append(installationTime).append(m.getInstallationTime()==null?"":"'").append(", ").append(m.getCollectDate()==null?"":"'").
                append(collectDate).append(m.getCollectDate()==null?"":"'").append(", ").append(m.getCollectTime()==null?"":"'").
                append(collectTime).append(m.getCollectTime()==null?"":"'").append(", ").
                append(m.getSurroundingsCulture().getIdSurroundingsCulture()).append(", ").
                append(m.getPlotNumber()).append(", ").
                append(m.getAmostralNumber()).append(", ").
                append(m.getColorPanTrap().getIdColorPanTrap()).append(", ").
                append(m.getSupportType().getIdSupportType()).append(", ").
                append(m.getFloorHeight()).append(", ").
                append(m.getWeight()).append(", ").
                append(m.getCollector().getIdCollector()).append(", "). 
                append(m.getWidth()).append(", ").
                append(m.getLength()).append(", ").
                append(m.getHeight()).append(", ").
                append(insertGeospatial).append(", ").
                append(insertLocality).append(", ").
                append(insertTaxonomic).append(", ").
                append(insertRecordLevel).append(", ").
                append(insertOccurrence).append(", ").
                append(m.getSurroundingsVegetation().getIdSurroundingsVegetation()).append(", '").
                append(m.getIdGeral()).append("'); ");

        return insertMonitoring.toString();
    }

    public String update(MonitoringDB m) {
        StringBuffer updateRecordLevel;
        StringBuffer updateLocality;
        StringBuffer updateOccurrence;
        StringBuffer updateTaxonomic;
        StringBuffer updateGeospatial;
        StringBuffer insertMonitoring;
        Date d = new Date();
        
        StringBuffer gui = new StringBuffer();
        gui.append(m.getRecordLevelElement().getInstitutionCode().getInstitutionCode()).append(":").
                append(m.getRecordLevelElement().getCollectionCode().getCollectionCode()).append(":").
                append(m.getOcurrenceElement().getCatalogNumber());
        SimpleDateFormat md = new SimpleDateFormat("yyyy-MM-dd");
        updateRecordLevel = new StringBuffer(" (SELECT updaterecordlevel_monitoring(");
        updateRecordLevel.append("'").append(md.format(new Date())).append("', '").
                append(m.getRecordLevelElement().getInstitutionCode().getIdInstitutionCode()).append("', '").
                append(m.getRecordLevelElement().getCollectionCode().getIdCollectionCode()).append("', '").
                append(m.getRecordLevelElement().getBasisOfRecord().getIdBasisOfRecord()).append("', '").
                append(gui).append("', ").
                append(m.getRecordLevelElement().getIdRecordLevelElement()).append(")) ");
        updateGeospatial = new StringBuffer(" (SELECT updategeospatial_monitoring");
        updateGeospatial.append("('").append(m.getGeospatialElement().getDecimalLatitude()).append("', '").
                append(m.getGeospatialElement().getDecimalLongitude()).append("', '").
                append(m.getGeospatialElement().getGeodeticDatum()).append("', '").
                append(m.getGeospatialElement().getReferencePoints()).append("', ").
                append(m.getGeospatialElement().getIdGeospatialElement()).append(")) ");
        updateLocality = new StringBuffer(" (SELECT updatelocality_monitoring('");
        updateLocality.append(m.getLocalityElement().getCoordinatePrecision()).append("', '").
                append(m.getLocalityElement().getCountry().getIdCountry()).append("', '").
                append(m.getLocalityElement().getLocality().getIdLocality()).append("', '").
                append(m.getLocalityElement().getLocationRemark()).append("', '").
                append(m.getLocalityElement().getMunicipality().getIdMunicipality()).append("', '").
                append(m.getLocalityElement().getStateProvince().getIdStateProvince()).append("', '").
                append(m.getLocalityElement().getVerbatimElevation()).append("', ").
                append(m.getLocalityElement().getIdLocalityElement()).append(")) ");
        StringBuffer hc = new StringBuffer();
        hc.append(m.getTaxonomicElement().getOrder().getOrder()).append(":").
                append(m.getTaxonomicElement().getTribe().getTribe()).append(":").
                append(m.getTaxonomicElement().getSubTribe().getSubTribe()).append(":").
                append(m.getTaxonomicElement().getFamily().getFamily()).append(":").
                append(m.getTaxonomicElement().getGenus().getGenus()).append(":").
                append(m.getTaxonomicElement().getSubGenus().getSubGenus()).append(":").
                append(m.getTaxonomicElement().getSpeciesName().getSpeciesName()).append(":").
                append(m.getTaxonomicElement().getSubSpecies().getSubSpecies()).append(":").
                append(m.getTaxonomicElement().getScientificName().getScientificName());
        updateTaxonomic = new StringBuffer(" (SELECT updatetaxonomic_monitoring");
        updateTaxonomic.append("('").append(m.getTaxonomicElement().getFamily().getIdFamily()).append("', '").
                append(m.getTaxonomicElement().getGenus().getIdGenus()).append("', '").
                append(m.getTaxonomicElement().getOrder().getIdOrder()).append("', '").
                append(m.getTaxonomicElement().getScientificName().getIdScientificName()).append("', '").
                append(m.getTaxonomicElement().getSpeciesName().getIdSpeciesName()).append("', '").
                append(m.getTaxonomicElement().getSubGenus().getIdSubGenus()).append("', '").
                append(m.getTaxonomicElement().getSubTribe().getIdSubTribe()).append("', '").
                append(m.getTaxonomicElement().getTribe().getIdTribe()).append("', '").
                append(m.getTaxonomicElement().getSubSpecies().getIdSubSpecies()).append("', '").
                append(m.getTaxonomicElement().getVernacularName()).append("','").
                append(hc).append("', ").
                append(m.getTaxonomicElement().getIdTaxonomicElement()).append(")) ");
        updateOccurrence = new StringBuffer(" (SELECT updateoccurrence_monitoring");
        updateOccurrence.append("('").append(m.getOcurrenceElement().getSex().getIdSex()).append("', '").
                append(m.getOcurrenceElement().getCatalogNumber()).append("', ").
                append(m.getOcurrenceElement().getIdOccurrenceElement()).append(")) ");
        insertMonitoring = new StringBuffer("UPDATE monitoring SET ");
        
        SimpleDateFormat sdfs = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat st = new SimpleDateFormat("HH:mm");
        
        StringBuffer installationDate = new StringBuffer();
        if(m.getInstallationDate()!=null)
            installationDate.append(sdfs.format(m.getInstallationDate()));            
        else
            installationDate.append("null");
        
        StringBuffer collectDate = new StringBuffer();
        if(m.getCollectDate()!=null)
            collectDate.append(sdfs.format(m.getCollectDate()));
        else
            collectDate.append("null");
        
        StringBuffer installationTime = new StringBuffer();
        if(m.getInstallationTime()!=null)
            installationTime.append(st.format(m.getInstallationTime()));
        else
            installationTime.append("null");
        
        StringBuffer collectTime = new StringBuffer();
        if(m.getCollectTime()!=null)
            collectTime.append(st.format(m.getCollectTime()));
        else
            collectTime.append("null");
        
        insertMonitoring.append("iddenomination = ").append(m.getDenomination().getIdDenomination()).
                append(", idtechnicalcollection = ").append(m.getTechnicalCollection().getIdTechinicalCollection()).
                append(", iddigitizer = ").append(m.getDigitizer().getIdDigitizer()).
                append(", idculture = ").append(m.getCulture().getIdCulture()).
                append(", idcultivar = ").append(m.getCultivar().getIdCultivar()).
                append(", idpredominantbiome = ").append(m.getPredominantBiome().getIdPredominantBiome()).
                append(", installationdate = ").append(m.getInstallationDate()==null?"":"'").append(installationDate).append(m.getInstallationDate()==null?"":"'").
                append(", installationtime = ").append(m.getInstallationTime()==null?"":"'").append(installationTime).append(m.getInstallationTime()==null?"":"'").
                append(", collectdate = ").append(m.getCollectDate()==null?"":"'").append(collectDate).append(m.getCollectDate()==null?"":"'").
                append(", collecttime = ").append(m.getCollectTime()==null?"":"'").append(collectTime).append(m.getCollectTime()==null?"":"'").
                append(", idsurroundingsculture = ").append(m.getSurroundingsCulture().getIdSurroundingsCulture()).
                append(", plotnumber = ").append(m.getPlotNumber()).
                append(", amostralnumber = ").append(m.getAmostralNumber()).
                append(", idcolorpantrap = ").append(m.getColorPanTrap().getIdColorPanTrap()).
                append(", idsupporttype = ").append(m.getSupportType().getIdSupportType()).
                append(", floorheight = ").append(m.getFloorHeight()).
                append(", weight = ").append(m.getWeight()).
                append(", idcollector = ").append(m.getCollector().getIdCollector()).
                append(", width = ").append(m.getWidth()).
                append(", length = ").append(m.getLength()).
                append(", height = ").append(m.getHeight()).
                append(", idgeospatialelement = ").append(updateGeospatial).                
                append(", idlocalityelement = ").append(updateLocality).
                append(", idtaxonomicelement = ").append(updateTaxonomic).
                append(", idrecordlevelelement = ").append(updateRecordLevel).                
                append(", idoccurrenceelement = ").append(updateOccurrence).
                append(", idsurroundingsvegetation = ").append(m.getSurroundingsVegetation().getIdSurroundingsVegetation()).
                append(", idgeral = '").append(m.getIdGeral()).                
                append("' WHERE idmonitoring = ").append(m.getIdMonitoring()).append("; ");                

        return insertMonitoring.toString();
    }
}
